﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using DTO;
using UTIL;

namespace DAO
{
    public class LoaiSanPhamDao
    {
        public static bool ThemLoaiSanPham(LoaiSanPhamDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_themLoaiSanPham");
                StoreUtilities.AddInputPara(cmd, "@tenloaisanpham", SqlDbType.NVarChar, one.TenLoaiSanPham);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;
        }

        public static bool XoaLoaiSanPham(LoaiSanPhamDto one)
        {
            bool result = false;
            try
            {
                List<SanPhamDto> dssp = SanPhamDao.LayDanhSachSanPhamTheoLoai(one.ID);
                if (dssp.Count != 0)
                {
                    LoaiSanPhamDto other = LayLoaiSanPhamOther();
                    foreach (SanPhamDto sp in dssp)
                    {
                        sp.IDLoai = other.ID;
                        SanPhamDao.CapnhatSanPham(sp);
                    }
                }
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_xoaLoaiSanPham");
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.ID);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }
        
        public static bool CapnhatLoaiSanPham(LoaiSanPhamDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_capnhatLoaiSanPham");
                StoreUtilities.AddInputPara(cmd, "@tenloaisanpham", SqlDbType.NVarChar, one.TenLoaiSanPham);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.ID);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }

        public static List<LoaiSanPhamDto> LayDanhSachLoaiSanPham()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachLoaiSanPham");
            SqlDataReader dr = cmd.ExecuteReader();

            List<LoaiSanPhamDto> danhsach = new List<LoaiSanPhamDto>();
            LoaiSanPhamDto loaisanpham = null;
            while (dr.Read())
            {
                loaisanpham = new LoaiSanPhamDto();
                loaisanpham.ID = (int)dr["ID"];
                loaisanpham.TenLoaiSanPham = (string)dr["tenloaisanpham"];
                
                danhsach.Add(loaisanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static bool KiemTraLoaiSanPhamTheoTen(String ten)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "Select * From LoaiSanPham where tenloaisanpham = '"+ten+"'";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return true;

            // dong ket noi
            cnn.Close();

            return false;
        }
        public static LoaiSanPhamDto LayLoaiSanPhamTheoID(int ID)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layLoaiSanPhamTheoID");
            StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, ID);
            SqlDataReader dr = cmd.ExecuteReader();

            LoaiSanPhamDto loaisanpham = null;
            while (dr.Read())
            {
                loaisanpham = new LoaiSanPhamDto();
                loaisanpham.ID = (int)dr["ID"];
                loaisanpham.TenLoaiSanPham = (string)dr["tenloaisanpham"];
            }
            // dong ket noi
            cnn.Close();

            return loaisanpham;
        }

        public static LoaiSanPhamDto LayLoaiSanPhamOther()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "select * from LoaiSanPham where tenloaisanpham = 'other'";
            SqlCommand cmd = new SqlCommand(sql, cnn);
           
            SqlDataReader dr = cmd.ExecuteReader();

            LoaiSanPhamDto loaisanpham = null;
            while (dr.Read())
            {
                loaisanpham = new LoaiSanPhamDto();
                loaisanpham.ID = (int)dr["ID"];
                loaisanpham.TenLoaiSanPham = (string)dr["tenloaisanpham"];
            }
            // dong ket noi
            cnn.Close();

            return loaisanpham;
        }

        public static List<LoaiSanPhamDto> LayLoaiSanPhamTheoKey(String key)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            
            string sql = "select * from LoaiSanPham where tenloaisanpham like N'%" + key + "%'";
            SqlCommand cmd =new SqlCommand(sql,cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<LoaiSanPhamDto> ds = new List<LoaiSanPhamDto>();
            LoaiSanPhamDto loaisanpham = null;
            while (dr.Read())
            {
                loaisanpham = new LoaiSanPhamDto();
                loaisanpham.ID = (int)dr["ID"];
                loaisanpham.TenLoaiSanPham = (string)dr["tenloaisanpham"];
                ds.Add(loaisanpham);
            }
            // dong ket noi
            cnn.Close();

            return ds;
        }

        public static bool KiemtraTontai(LoaiSanPhamDto one)
        {
            bool result = false;
            LoaiSanPhamDto temp = LayLoaiSanPhamTheoID(one.ID);
            if (temp != null)
                result = true;

            return result;
        }
    }
}
